package sf.database.dialect.db;


import sf.core.DBField;
import sf.database.dbinfo.Feature;
import sf.database.dbinfo.SequenceInfo;
import sf.database.dialect.DBDialect;
import sf.database.dialect.DBProperty;
import sf.database.jdbc.sql.Crud;
import sf.database.meta.ColumnMapping;
import sf.database.meta.TableMapping;
import sf.database.support.DBMS;
import sf.querydsl.QueryDSLSupportDatabase;
import sf.tools.JavaTypeUtils;
import sf.tools.StringUtils;
import sf.tools.SystemUtils;

import java.sql.Connection;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * sqlserver
 */
public class SqlServerDialect extends DBDialect {
    @Override
    public String getName() {
        return DBMS.sqlserver.name();
    }

    @Override
    public int getNumber() {
        return DBMS.sqlserver.getNumber();
    }
    // private static String COMMENT_TABLE =
    // "EXECUTE sp_updateextendedproperty N'Description', '$tableComment', N'user', N'dbo', N'table', N'$table', NULL, NULL";

    private static String COMMENT_COLUMN = "EXECUTE sp_addextendedproperty N'MS_Description', '%3$s', N'user', N'dbo', N'table', N'%1$s', N'column', N'%2$s'";


    public SqlServerDialect() {
        features.add(Feature.COLUMN_DEF_ALLOW_NULL);
        features.add(Feature.CONCAT_IS_ADD);
        features.add(Feature.NOT_SUPPORT_KEYWORD_DEFAULT);
        features.add(Feature.BATCH_GENERATED_KEY_BY_FUNCTION);
        features.add(Feature.SUPPORT_IDENTITY);
        features.add(Feature.SUPPORT_SQLSERVER_COMMENT); //sqlserver 的注释语法比较独特
        // features.add(Feature.NO_BIND_FOR_INSERT);
        // features.add(Feature.NO_BIND_FOR_SELECT);

        setProperty(DBProperty.ADD_COLUMN, "ADD");
        setProperty(DBProperty.MODIFY_COLUMN, "ALTER COLUMN");
        setProperty(DBProperty.DROP_COLUMN, "DROP COLUMN");
        setProperty(DBProperty.ADD_CONSTRAINT, "ADD CONSTRAINT");
        setProperty(DBProperty.CHECK_SQL, "select 1");
        setProperty(DBProperty.GET_IDENTITY_FUNCTION, "SELECT @@IDENTITY");
        setProperty(DBProperty.WRAP_FOR_KEYWORD, "[]");
        setProperty(DBProperty.DROP_INDEX_TABLE_PATTERN, "%2$s.%1$s");
        setProperty(DBProperty.NVL_FUNCTION, "isnull(%1$s,%2$s)");
    }

    @Override
    protected String unwrapper(String name) {
        return StringUtils.replaceEach(name, new String[]{"[", "]"}, new String[]{"", ""});
    }

    @Override
    public String getDefaultSchema() {
        return "dbo";
    }

    @Override
    public String getSqlStatmentSeparator() {
        return "go" + SystemUtils.lineSeparator;
    }

    @Override
    public String sqlTableDrop(String table) {
        String sql = "if exists (select * from dbo.sysobjects where id=object_id(N'%s') and objectproperty(id,N'IsUserTable')=1) drop table %s;";
        table = wrapKeyword(table);
        return String.format(sql, table, table);
    }

    @Override
    public String sqlTableRename(String oldName, String newName) {
        oldName = wrapKeyword(oldName);
        newName = wrapKeyword(newName);
        return "alter table  " + oldName + " rename to " + newName + ";";
    }

    @Override
    public String sqlColumnRename(String table, String oldColumnName, String newColumnName) {
        return null;
    }

    @Override
    public String sqlColumnAdd(String table, String column_definition, String column_position) {
        return String.format("alter table %s add %s;", wrapKeyword(table), column_definition);
    }

    @Override
    public String sqlColumnModify(String table, String column_definition, String column_position) {
        return String.format("alter table %s alter column %s;", wrapKeyword(table), column_definition);
    }

    @Override
    public String sqlColumnDrop(String table, String column) {
        return String.format("alter table %s drop column %s;", wrapKeyword(table), wrapKeyword(column));
    }

    @Override
    public StringBuilder sqlPageList(StringBuilder sql, long offset, int limit) {
        if (offset == 0) {
            sql.insert(0, "select top " + limit + " * from (").append(") as temp");
        } else {
            throw new UnsupportedOperationException("not support,please use you own count sql and page sql!");
        }
        return sql;
    }

    @Override
    public String getHibernateDialect() {
        return "org.hibernate.dialect.SQLServerDialect";
    }

    public boolean createEntity(Connection conn, TableMapping en) {
        StringBuilder sb = new StringBuilder("CREATE TABLE " + wrapKeyword(en.getTableName()) + "(");
        // 创建字段
        boolean delimiter = false;
        for (Map.Entry<DBField, ColumnMapping> entry : en.getSchemaMap().entrySet()) {
            ColumnMapping cm = entry.getValue();
            if (cm.getJpaTransient() != null) {
                continue;
            }
            sb.append(delimiter ? "," : "").append(SystemUtils.lineSeparator);
            sb.append(wrapKeyword(cm.getRawColumnName())).append(' ').append(evalFieldDefinition(cm));
            // 自增主键特殊形式关键字
            if (cm.isPk() && cm.getGv() != null) {
                if (JavaTypeUtils.isNumberClass(cm.getClz())) {
                    sb.append(" IDENTITY(1,1) ");
                }
            }
            delimiter = true;
        }
        // 创建主键
        List<ColumnMapping> pks = en.getPkFields();
        if (!pks.isEmpty()) {
            sb.append(",").append(SystemUtils.lineSeparator);
            sb.append("PRIMARY KEY (");
            boolean f = false;
            for (ColumnMapping pk : pks) {
                sb.append(f ? "," : "").append(wrapKeyword(pk.getRawColumnName()));
                f = true;
            }
            sb.append(") ").append(SystemUtils.lineSeparator);
        }
        // 结束表字段设置
        sb.append(')');

        List<String> sqls = new ArrayList<>();
        sqls.add(sb.toString());

        // // 处理非主键unique
        // for (MappingField mf : en.getMappingFields()) {
        // if(!mf.isPk())
        // continue;
        // String sql =
        // gSQL("alter table ${T} add constraint unique_key_${F} unique (${F});",
        // en.getTableName(),mf.getColumnName());
        // sqls.add(Sqls.create(sql));
        // }
        // 处理AutoIncreasement

        // 创建索引
        sqls.addAll(createIndexSql(en));

        //创建约束
        sqls.addAll(createUniqueSql(en));

        // 添加注释(表注释与字段注释)
        sqls.addAll(addCommentSql(en));
        // 执行创建语句
        execute(sqls, conn);

        // 创建关联表
        createRelation(conn, en);

        return true;
    }

    @Override
    public String evalFieldType(ColumnMapping mf) {
        String type = "";
        int length = getColumnLength(mf);
        int scale = getColumnScale(mf);
        int precision = getColumnPrecision(mf);
        switch (mf.getSqlType()) {
            case Types.CHAR: {
                type = "CHAR(" + length + ")";
                break;
            }
            case Types.VARCHAR: {
                type = "VARCHAR(" + length + ")";
                break;
            }
            case Types.TINYINT: {
                type = "TINYINT";
                break;
            }
            case Types.SMALLINT: {
                type = "SMALLINT";
                break;
            }
            case Types.INTEGER: {
                // 用户自定义了宽度
                if (length > 0 && length <= 38) {
                    type = "NUMERIC(" + length + ")";
                } else {
                    // 用数据库的默认宽度
                    type = "INT";
                }
                break;
            }
            case Types.BIGINT: {
                type = "BIGINT";
                break;
            }
            case Types.FLOAT: {
                // 用户自定义了精度
                if (precision > 0 && scale > 0) {
                    type = "DECIMAL(" + precision + "," + scale + ")";
                } else {
                    type = "FLOAT";
                }
                break;
            }
            case Types.REAL:
                type = "REAL";
                break;
            case Types.DOUBLE:
                // 用默认精度
                type = "DECIMAL(15,10)";
                break;
            case Types.BIT:
            case Types.BOOLEAN: {
                type = "BIT";
                break;
            }
            case Types.DECIMAL: {
                // BigDecimal
                if (scale > 0 && precision > 0) {
                    type = "DECIMAL(" + precision + "," + scale + ")";
                } else {
                    throw new UnsupportedOperationException("Unsupport type!");
                }
                break;
            }
            case Types.NUMERIC: {
                if (precision > 0) {
                    type = "NUMERIC(" + precision + "," + scale + ")";
                } else {
                    type = "NUMERIC(38,0)";
                }
                break;
            }
            case Types.DATE:
            case Types.TIME:
            case Types.TIMESTAMP:
                type = "DATETIME";
                break;
            case Types.TIME_WITH_TIMEZONE:
            case Types.TIMESTAMP_WITH_TIMEZONE:
                type = "DATETIMEOFFSET";//该特性必须是2008及以上
                break;
            case Types.BLOB:
            case Types.BINARY:
                type = "BINARY";
                break;
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
                type = "VARBINARY(max)";
                break;
            case Types.CLOB:
            case Types.LONGVARCHAR:
            case Types.LONGNVARCHAR: {
                type = "TEXT";
                break;
            }
            default:
                type = super.evalFieldType(mf);
                break;
        }
        return type;
    }

    @Override
    public boolean dropEntity(Connection conn, TableMapping en) {
        return super.dropEntity(conn, en);
    }

    @Override
    public List<String> addCommentSql(TableMapping en) {
        // TODO 表注释 SQLServer2005中貌似不行
        // 字段注释
        List<String> sqls = new ArrayList<String>();
        if (!en.getSchemaMap().isEmpty()) {

            for (ColumnMapping mf : en.getSchemas()) {
                if (StringUtils.isNotBlank(mf.getComment())) {
                    //table,column,columnComment
                    //表名和字段名不能使用[]包括.
                    String s = addColumnCommnetSql(unwrapper(en.getTableName()), unwrapper(mf.getRawColumnName()), mf.getComment());
                    sqls.add(s);
                }
            }
        }
        return sqls;
    }

    @Override
    public String addColumnCommnetSql(String tableName, String columnName, String comment) {
        return String.format(COMMENT_COLUMN, tableName, columnName, comment);
    }

    @Override
    public String uniqueSql(String tableName, String name, String[] columnNames) {
        StringBuilder sb = new StringBuilder();
        sb.append("alter table ").append(wrapKeyword(tableName)).append(" add constraint ").append(name).append(" unique(");
        for (int i = 0; i < columnNames.length; i++) {
            String column = columnNames[i];
            sb.append(i > 0 ? "," : "").append(wrapKeyword(column));
        }
        sb.append(")");
        return sb.toString();
    }

    @Override
    public List<SequenceInfo> getSequenceInfo(Connection conn, String schema, String seqName) {
        schema = StringUtils.isBlank(schema) ? "%" : schema;
        seqName = StringUtils.isBlank(seqName) ? "%" : seqName;
        String sql = "SELECT CONVERT(varchar,seq.name),CONVERT(int,seq.cache_size),"
                + "CONVERT(bigint,seq.current_value),"
                + "CONVERT(bigint,seq.minimum_value),"
                + "CONVERT(bigint,seq.start_value),"
                + "CONVERT(int,seq.increment),"
                + "CONVERT(varchar,m.name) as schema_name from sys.sequences seq, sys.schemas m WHERE seq.SCHEMA_ID=m.SCHEMA_ID "
                + "AND seq.name LIKE ? AND m.name LIKE ?";
        List<SequenceInfo> result = new ArrayList<>();
        Crud.getInstance().getCrudSql().selectResultSet(conn, (rs) -> {
            while (rs.next()) {
                SequenceInfo seq = new SequenceInfo();
                seq.setCatalog(null);
                seq.setSchema(rs.getString(1));
                seq.setName(rs.getString(2));
                seq.setMinValue(rs.getLong(3));
                //	seq.setMaxValue(rs.getLong(4));
                seq.setStep(rs.getInt(5));
                seq.setCacheSize(rs.getInt(6));
                seq.setCurrentValue(rs.getLong(7));
                result.add(seq);
            }
            return null;
        }, sql, schema, seqName);
        return result;
    }

    @Override
    public String getQueryDslDialect() {
        return QueryDSLSupportDatabase.SQLServer;
    }

    @Override
    public String getJooqDialect() {
        return null;
    }
}
